﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using OgilvyOne.IDAL;
using OgilvyOne.Model;
using System.Data;
using System.Data.SqlClient;

namespace OgilvyOne.SqlServerDAL
{
    public class BrandOther: BrandOtherIDAL
    {
        string connstr = SQLHelper.CONN_STRING_NON_DTC;
        /// <summary>
        /// 全部关联信息列表
        /// </summary>
        /// <returns></returns>
        public IList GetList(string where)
        {
            string strSql = "select a.ID,a.BRAND_ID,a.ITEM_ID,a.BUS_IDS,a.COM_IDS,b.cnname,c.BRAND_NAME from COFCO_BRAND_OTHER a,CMS_DICTIONARY b,COFCO_BRAND_INFO c where a.brand_id=c.BRAND_ID and a.item_id=b.CID ";
            if(where!="")
            {
                strSql += " and " + where;
            }
            IList datalist = new ArrayList();
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql, new SqlParameter[] { }))
            {
                while (rdr.Read())
                {
                    BrandOtherModel bom = new BrandOtherModel();
                    bom.Id = rdr.GetInt32(0);
                    bom.BrandId = rdr.GetInt32(1);
                    bom.ItemId = rdr.GetInt32(2);
                    bom.BusIds = rdr.GetString(3);
                    bom.ComIds = rdr.GetString(4);
                    bom.ItemName = rdr.GetString(5);
                    bom.BrandName = rdr.GetString(6);
                    datalist.Add(bom);
                }
            }
            return datalist;
        }
        /// <summary>
        /// 关联信息
        /// </summary>
        /// <returns></returns>
        public BrandOtherModel Get(int id)
        {
            string strSql = "select a.ID,a.BRAND_ID,a.ITEM_ID,a.BUS_IDS,a.COM_IDS,b.cnname,c.BRAND_NAME from COFCO_BRAND_OTHER a,CMS_DICTIONARY b,COFCO_BRAND_INFO c where a.brand_id=c.BRAND_ID and a.item_id=b.CID and id = "+ id;
            BrandOtherModel bom = new BrandOtherModel();
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
               };
                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql, parms))
                {
                    if (rdr.Read())
                    {
                        bom.Id = rdr.GetInt32(0);
                        bom.BrandId = rdr.GetInt32(1);
                        bom.ItemId = rdr.GetInt32(2);
                        bom.BusIds = rdr.GetString(3);
                        bom.ComIds = rdr.GetString(4);
                        bom.ItemName = rdr.GetString(5);
                        bom.BrandName = rdr.GetString(6);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return bom;
        }
        /// <summary>
        /// 添加关联信息
        /// </summary>
        /// <returns></returns>
        public int Add(BrandOtherModel setDic)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into COFCO_BRAND_OTHER (BRAND_ID,ITEM_ID,BUS_IDS,COM_IDS) ");
            strSql.Append(" values (@BRAND_ID,@ITEM_ID,@BUS_IDS,@COM_IDS)");
            SqlParameter[] parameters = {
                    new SqlParameter("@BRAND_ID",SqlDbType.Int),
                    new SqlParameter("@ITEM_ID",SqlDbType.Int),
                    new SqlParameter("@BUS_IDS",SqlDbType.VarChar,100),
                    new SqlParameter("@COM_IDS",SqlDbType.VarChar,100),
                    };
            parameters[0].Value = setDic.BrandId;
            parameters[1].Value = setDic.ItemId;
            parameters[2].Value = setDic.BusIds;
            parameters[3].Value = setDic.ComIds;

            int ts = 1;
            return ts = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 删除一条关联信息
        /// </summary>
        /// <returns></returns>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from COFCO_BRAND_OTHER ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
             };
            parameters[0].Value = id;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }
        /// <summary>
        /// 修改关联信息
        /// </summary>
        /// <returns></returns>
        public int Update(BrandOtherModel updDic)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE COFCO_BRAND_OTHER SET BRAND_ID=@BRAND_ID,ITEM_ID=@ITEM_ID,BUS_IDS=@BUS_IDS,COM_IDS=@COM_IDS WHERE id = @id");
            SqlParameter[] parameters = {
                    new SqlParameter("@BRAND_ID",SqlDbType.Int),
                    new SqlParameter("@ITEM_ID",SqlDbType.Char,10),
                    new SqlParameter("@BUS_IDS",SqlDbType.VarChar,100),
                    new SqlParameter("@COM_IDS",SqlDbType.VarChar,700),
                    new SqlParameter("@id",SqlDbType.Int)
                   };
            parameters[0].Value = updDic.BrandId;
            parameters[1].Value = updDic.ItemId;
            parameters[2].Value = updDic.BusIds;
            parameters[3].Value = updDic.ComIds;
            parameters[4].Value = updDic.Id;
         
            int ts = 1;
            return ts = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
        }
    }
}
